

df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
aaa=sqlContext.sql("""
    create table beauty.yichang as
    select
        *
    FROM
        base
    where city ='宜昌市' AND  status =3 AND
      (msg_content LIKE '%美柚%'
      or msg_content LIKE '%大姨吗%'
      or msg_content LIKE '%新氧%'
      or msg_content LIKE '%悦美%'
      or msg_content LIKE '%更美%'
      or  msg_content LIKE '%小红书%'
      or msg_content LIKE '%美丽说%'
      or msg_content LIKE '%蘑菇街%'
      or  msg_content LIKE '%聚美优品%'
      or msg_content like '%洋码头%'
      or msg_content like '%女士%'
      or msg_content like '%小姐%'
      or msg_content like '%小姐姐%'
      or msg_content like '%洋码头%'
      or msg_content LIKE '%唯品会%')

""")

http://www.soyoung.com
https://www.yuemei.com
http://www.igengmei.com

import pandas as pd
aaa=sqlContext.sql("""
SELECT
    DISTINCT  t.msisdn
FROM (
    select
        msisdn
    FROM
        dw.beauty_yichang
    WHERE unix_timestamp(insert_time) >=1525104000
) t
 LEFT OUTER JOIN
 history.meirong t2
 ON t.msisdn = t2.msisdn
            WHERE t2.msisdn is null
 limit 50000
""")
writer = pd.ExcelWriter("/home/games_data/result/20180907/nvxing_yichang_20181031.xlsx",engine='xlsxwriter')
aaa.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()


# load data local inpath '/home/data/zeng/2018105.txt.txt' into table history.meirong partition(pt='20181105');



